package com.fr.data;

import com.fr.base.FRContext;
import com.fr.base.Parameter;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SalesReportWeek extends AbstractTableData {
    // 列名数组，保存程序数据集所有列名
    private String[] columnNames = null;
    // 定义程序数据集的列数量
    private int columnNum = 10;
    // 保存查询表的实际列数量
    private int colNum = 0;
    // 保存查询得到列值
    private ArrayList valueList = null;

    // 构造函数，定义表结构，该表有10个数据列，列名为column#0，column#1，。。。。。。column#9
    public SalesReportWeek() {
        // 定义tableName参数
//        setDefaultParameters(new Parameter[]{new Parameter("tableName")});
        // 定义程序数据集列名
        columnNames = new String[columnNum];
        for (int i = 0; i < columnNum; i++) {
            columnNames[i] = "column#" + String.valueOf(i);
        }
    }

    // 实现其他四个方法

    @Override
    public int getColumnCount() {
        return columnNum;
    }

    @Override
    public String getColumnName(int columnIndex) {
        return columnNames[columnIndex];
    }

    @Override
    public int getRowCount() {
        init();
        return valueList.size();
    }

    @Override
    public Object getValueAt(int rowIndex, int columnIndex) {
        init();
        if (columnIndex >= colNum) {
            return null;
        }
        return ((Object[]) valueList.get(rowIndex))[columnIndex];
    }

    // 准备数据
    public void init() {
        // 确保只被执行一次
        if (valueList != null) {
            return;
        }
        // 保存得到的数据库表名
//        String tableName = parameters[0].getValue().toString();
        // 构造SQL语句,并打印出来
        //查询老店经销商Id
        StringBuffer oldStorePartyId = new StringBuffer();
        oldStorePartyId.append("SELECT s.party_id FROM zdev_store_info s WHERE s.store_status_id = 'OPEN' AND  SYSDATE-3 > to_date(s.extend1，'yyyyMMdd') GROUP BY s.party_id ");
        //查询今年本周数据
        StringBuffer yearWeekData = new StringBuffer();
        yearWeekData.append("  SELECT  V2.YEAR, V2.QUANTITY, v2.day,  v2.week_day,SUM(V2.QUANTITY) QUANTITY, ");
        yearWeekData.append(" SUM（V2.QUANTITY1）CUSTOMER_AMOUNT,round(SUM(V2.QUANTITY) / ( CASE WHEN sum(V2.STORE_AMOUNT) = 0 THEN 1 ELSE sum(V2.STORE_AMOUNT) END ),2) STORE_AMOUNT,");
        yearWeekData.append(" round(SUM(V2.QUANTITY) / sum(V2.Quantity1),2) unitPrice,SUM(V2.QUANTITY2) COPIES, v2.sales_yony, v2.sales_monm");
        yearWeekData.append("  FROM ZDEV_SALES_H_V2 V2");
        yearWeekData.append(" WHERE v2.dim_type = '01'  AND v2.week = '26'");
        yearWeekData.append(" GROUP BY V2.YEAR, V2.QUANTITY,v2.day,  v2.week_day, v2.sales_yony, v2.sales_monm ");
        yearWeekData.append("  ORDER BY v2.year DESC,v2.month DESC ,v2.day DESC ");
        FRContext.getLogger().info("Query SQL of SalesReportWeek: \n" + yearWeekData);
        // 保存得到的结果集
        valueList = new ArrayList();
        // 下面开始建立数据库连接，按照刚才的SQL语句进行查询
        Connection conn = ConnectUtil.getConnection();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(yearWeekData.toString());
            // 获得记录的详细信息，然后获得总列数
            ResultSetMetaData rsmd = rs.getMetaData();
            colNum = rsmd.getColumnCount();
            // 用对象保存数据
            Object[] objArray = null;
            while (rs.next()) {
                objArray = new Object[colNum];
                for (int i = 0; i < colNum; i++) {
                    objArray[i] = rs.getObject(i + 1);
                }
                // 在valueList中加入这一行数据
                valueList.add(objArray);
            }
            // 释放数据库资源
            rs.close();
            stmt.close();
            conn.close();
            // 打印一共取到的数据行数量
            FRContext.getLogger().info(
                    "Query SQL of TestGetDataByTableName: \n" + valueList.size()
                            + " rows selected");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    // 释放一些资源，因为可能会有重复调用，所以需释放valueList，将上次查询的结果释放掉
    @Override
    public void release() throws Exception {
        super.release();
        this.valueList = null;
    }
}